﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;
using System.Configuration;
using System.Data.OleDb;
using System.Data;

namespace Arch.DBLINK
{
    //---一些额外方法

    public static class Excel_EXT
    {
        /* 
         * 规则：表名应该用[]，且内部名称以$结尾，不区分大小写。
         * 有数字开头的 或者一些特殊表名，应加''
         * 没有列名的默认以F1为第一列……
         * */
        /// <summary>
        /// 根据用户传入的表名，获取完整的适用于查询的完整表名，形如[Sheet1$A1:Z9]
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="Begin">开始的单元格，如A1</param>
        /// <param name="End">结束的单元格，如Z9</param>
        /// <returns></returns>
        public static string ToSafeTableName(this string tableName, string Begin = null, string End = null)
        {
            //包含[]则删除
            if (tableName.Contains('[') && tableName.Contains(']'))
            {
                tableName = tableName.Remove(tableName.Length - 1, 1);
                tableName = tableName.Remove(0, 1);
            }
            // 如果不包含$结尾，则加上
            if (!tableName.EndsWith("$"))
            {
                tableName += '$';
            }
            // 如果带有开始和结束的单元格，则加上
            if (!string.IsNullOrEmpty(Begin) && !string.IsNullOrEmpty(End))
            {
                tableName += $"{Begin}:{End}";
            }
            tableName = "[" + tableName + "]";
            // 最后结果：[Sheet1$A1:Z9]
            return tableName;
        }

        /// <summary>
        /// 判断对应路径是否存在Excel
        /// </summary>
        /// <param name="FilePath">完整路径，必须包含扩展名。否则为false</param>
        /// <returns></returns>
        public static bool ExistFile(this string FilePath)
        {
            if (File.Exists(FilePath))
            {
                string extension = Path.GetExtension(FilePath);
                if (!string.IsNullOrEmpty(extension))
                {
                    if (extension == ".xls" || extension == ".xlsx")
                    {
                        return true;
                    }
                }
            }
            return false;
        }

        /// <summary>
        /// 打开Excel exe应用程序
        /// </summary>
        /// <param name="FilePath">路径</param>
        public static void Open(this string FilePath)
        {
            Process.Start(FilePath);
        }
    }

    //---配置连接

    public partial class Excel
    {
        /// <summary>
        ///  一个连接字符串的最简单的示例。请不要修改。
        /// </summary>
        public const string ForExample_CntStrSimple = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F://1.xlsx;";
        /// <summary>
        /// 一个连接字符串的示例。请不要修改。
        /// </summary>
        public const string ForExample_CntStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F://1.xlsx;Persist Security Info=False;Extended Properties='Excel 12.0;HDR=YES;IMEX=2;';";

        /// <summary>
        /// OleDb枚举：Excel03及以下选用03,Excel07及以上选用07
        /// </summary>
        public enum OleDb
        {
            Version2003, Version2007
        }
        /// <summary>
        /// Extended Properties IMEX：链接使用用途的枚举
        /// </summary>
        public enum IMEX
        {
            /// <summary>
            /// 打开的链接【读写】均可。
            /// </summary>
            ReadAndWrite = 2,
            /// <summary>
            /// 打开的链接只能用于【写入】。
            /// </summary>
            OnlyWrite = 0,
            /// <summary>
            /// 打开的链接只能用于【读取】。
            /// </summary>
            OnlyRead = 1,
        }

        public Excel(string FilePath)
        {
            this.FilePath = FilePath;
            UpdateConnectionString();
        }
        /// <summary>
        /// 返回一个新实例
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public static Excel New(string FilePath)
        {
            return new Excel(FilePath);
        }

        /// <summary>
        /// 连接字符串；
        /// 支持寻找配置文件的connectionStrings、配置文件的AppSettings
        /// </summary>
        public string ConnectionString { get; private set; }
        /// <summary>
        /// 设置属性后，最后调用它
        /// </summary>
        /// <returns></returns>
        private void UpdateConnectionString()
        {
            if (!string.IsNullOrEmpty(FilePath))
            {
                StringBuilder strbd = new StringBuilder();
                strbd.Append($"Provider ={Provider};");
                strbd.Append($"Data Source={FilePath};");
                strbd.Append($"Persist Security Info={SecurityInfo};");
                strbd.Append($"Extended Properties='{InnerVersion};HDR={(FirstRowIsHead ? "YES" : "No")};IMEX={(int)RWMode};';");
                ConnectionString = strbd.ToString();
            }
            //throw new ArgumentException("参数为空");
        }

        private string filepath;
        /// <summary>
        /// Excel文件的物理路径
        /// </summary>
        public string FilePath
        {
            get { return filepath; }
            set { filepath = value; UpdateConnectionString(); }
        }

        private OleDb version = OleDb.Version2007;
        /// <summary>
        /// 连接引擎的Excel版本
        /// </summary>
        public OleDb Version
        {
            get { return version; }
            set { version = value; UpdateConnectionString(); }
        }
        /// <summary>
        /// 根据OleDb版本设置的Provider的字符串
        /// </summary>
        public string Provider
        {
            get
            {
                if (Version == OleDb.Version2003)
                    return "Microsoft.Jet.OleDb.4.0";
                else
                    return "Microsoft.ACE.OLEDB.12.0";
            }
        }
        /// <summary>
        /// 根据OleDb版本设置的内部版本号
        /// </summary>
        public string InnerVersion
        {
            get
            {
                if (Version == OleDb.Version2003)
                    return "Excel 11.0";
                else
                    return "Excel 12.0";
            }
        }

        private bool security = false;
        /// <summary>
        /// 指明是否采用集成安全机制（ADO在数据库连接成功后是否保存密码信息）（默认不采用）
        /// </summary>
        public bool SecurityInfo
        {
            get { return security; }
            set { security = value; UpdateConnectionString(); }
        }

        private bool hdr = true;
        /// <summary>
        /// HDR：是否将第一行作为字段名。默认作为字段名使用。
        /// </summary>
        public bool FirstRowIsHead
        {
            get { return hdr; }
            set { hdr = value; UpdateConnectionString(); }
        }

        private IMEX rw = IMEX.OnlyRead;
        /// <summary>
        /// 设置链接是否只读、只写、读写。默认为只读。
        /// </summary>
        public IMEX RWMode
        {
            get { return rw; }
            set { rw = value; UpdateConnectionString(); }
        }

        private int timeout = 30;
        /// <summary>
        /// 设置超时，默认30秒
        /// </summary>
        public int Timeout
        {
            get { return timeout; }
            set { timeout = value; UpdateConnectionString(); }
        }

        private CommandType cmdtype = CommandType.Text;
        /// <summary>
        /// 指定是查询文本还是存储过程
        /// </summary>
        private CommandType CmdType
        {
            get { return cmdtype; }
            set { cmdtype = value; UpdateConnectionString(); }
        }
    }

    //---逻辑调用

    public partial class Excel
    {
        ///// <summary>
        ///// 向Excel末尾新写入一行
        ///// </summary>
        ///// <param name="TableName">写入的表名</param>
        ///// <param name="Values">写入的值（必须保证与列数相同）</param>
        ///// <returns>是否成功</returns>
        //public bool AppendALine(string TableName, params string[] Values)
        //{
        //    SQL_Insert sql = new SQL_Insert(TableName.ToSafeTableName());
        //    sql.AddRange(Values);
        //    return NonQuery(sql.ToString()) == 1 ? true : false;
        //}

        //private SQL_Insert writeMore;
        ///// <summary>
        ///// 【写入很多行】准备开始写入很多行
        ///// </summary>
        ///// <param name="TableName"></param>
        //public void StartAppendLines(string TableName)
        //{
        //    writeMore = new SQL_Insert(TableName.ToSafeTableName());
        //}
        ///// <summary>
        /////【写入很多行】 写入一行的所有值 有问题
        ///// </summary>
        ///// <param name="Values">一行的所有值</param>
        //public void AppendLinesOne(params string[] Values)
        //{
        //    if (writeMore != null)
        //    {
        //       // writeMore.AddRowsOne(Values);
        //    }
        //}
        ///// <summary>
        /////【写入很多行】结束执行语句
        ///// </summary>
        ///// <returns></returns>
        //public bool EndAppendLines()
        //{
        //    if (writeMore != null)
        //    {
        //        return NonQuery(writeMore.ToString()) == 1 ? true : false;
        //    }
        //    return false;
        //}
    }



    //---高层调用

    public partial class Excel
    {
        /// <summary>
        /// [Select]获得指定查询的结果表
        /// </summary>
        /// <param name="Sql">SQL语句或者存储过程名</param>
        /// <param name="action">进行参数化赋值</param>
        /// <returns></returns>
        public DataTable Query(string Sql, Action<OleDbParameterCollection> action = null)
        {
            CmdType = CommandType.Text;
            return Adapter_Fill(Sql, action);
        }       

        /// <summary>
        ///  返回表中所有数据。适用于小型表。
        /// </summary>
        /// <param name="TableName">表名。如果没有传入表名，默认为Sheet1。</param>
        /// <returns></returns>
        public DataTable QueryTable(string TableName)
        {
            string s = "select * from " + (TableName ?? "Sheet1").ToSafeTableName();
            return Query(s);
        }

        /// <summary>
        /// 返回指定单元格行列的部分表
        /// 是否要将返回的单元格首行作为标题，请设置ExcelSelect的FirstIsField属性。
        /// </summary>
        /// <param name="TableName">表名。如果没有传入表名，默认为Sheet1。</param>
        /// <param name="BeginCell">开始的单元格，如A1</param>
        /// <param name="EndCell">结束的单元格，如Z9</param>
        /// <returns></returns>
        public DataTable QueryTable(string TableName, string BeginCell, string EndCell)
        {
            return Query("select * from " + (TableName ?? "Sheet1").ToSafeTableName(BeginCell, EndCell));
        }

        /// <summary>
        /// 返回第一行第一列的值。适用于聚合函数。
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <param name="action">进行参数化赋值</param>
        /// <returns></returns>
        public object QueryScalar(string Sql, Action<OleDbParameterCollection> action = null)
        {
            CmdType = CommandType.Text;
            return ExecuteScalar(Sql, action);
        }
       
        /// <summary>
        /// 看该查询是否命中行（判断查询结果是否为空）
        /// </summary>
        /// <param name="SQL">SQL语句或者存储过程名</param>
        /// <param name="action">进行参数化赋值</param>
        /// <returns></returns>
        public bool QueryExist(string SQL, Action<OleDbParameterCollection> action = null)
        {
            return QueryScalar(SQL, action) == null ? false : true;
        }

        /// <summary>
        /// 执行非查询的方法（不要用于select语句）
        /// </summary>
        /// <param name="SQL">SQL语句或者存储过程名</param>
        /// <param name="action">进行参数化赋值</param>
        /// <returns></returns>
        public int NonQuery(string SQL, Action<OleDbParameterCollection> action = null)
        {
            CmdType = CommandType.Text;
            return ExecuteNonQuery(SQL, action);
        }
        
        /// <summary>
        /// 看该查询是否命中行
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public bool Exist(string SQL)
        {
            return ExecuteScalar(SQL) == null ? false : true;
        }



        ///// <summary>
        ///// 返回整个工作簿所组成的DataSet（适用于表个数比较小）
        ///// </summary>
        ///// <param name="WithPostfix">表名最后是否含有$</param>
        ///// <returns></returns>
        //public DataSet GetAllTables(bool WithPostfix = true)
        //{
        //    DataSet ds = new DataSet();
        //    List<string> worksheetsName = GetAllWorkSheetsName(WithPostfix);
        //    for (int i = 0; i < worksheetsName.Count; i++)
        //    {
        //        ds.Tables.Add(GetWholeTable(worksheetsName[i]));
        //        ds.Tables[i].TableName = worksheetsName[i];
        //    }
        //    return ds;
        //}

      
        /// <summary>
        /// 返回所有工作表的名字所组成的列表
        /// </summary>
        /// <param name="WithPostfix">表名是否含有$</param>
        /// <returns></returns>
        public List<string> GetAllWorkSheetsName(bool WithPostfix = true)
        {
            try
            {
                DataTable dt = GetAllTableInfo();
                List<string> lists = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string a = dt.Rows[i]["TABLE_NAME"].ToString();
                    //如果包含'即为数字开头的表，如'3Y'，则去掉前后引号
                    if (a.Contains("'"))
                    {
                        a = a.Remove(0, 1);
                        a = a.Remove(a.Length - 1, 1);
                    }
                    if (!WithPostfix)
                    {
                        a = a.Remove(a.Length - 1, 1);
                    }
                    lists.Add(a);
                }
                return lists;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

    }

    //---底层执行

    public partial class Excel
    {
        /// <summary>
        /// 检查该SQL是否select语句
        /// </summary>
        /// <param name="SQL"></param>
        private bool Check_IsSelect(string SQL)
        {
            if (!SQL.ToLower().Trim().StartsWith("select"))
            {
                throw new ArgumentException("必须是select语句！");
            }
            return true;
        }
        /// <summary>
        /// 检查该SQL不是select语句
        /// </summary>
        /// <param name="SQL"></param>
        private bool Check_IsNotSelect(string SQL)
        {
            if (!SQL.ToLower().Trim().StartsWith("select"))
            {
                throw new ArgumentException("必须不是select语句！");
            }
            return true;
        }
        /// <summary>
        /// 检查Cnt和SQL是否为空
        /// </summary>
        /// <param name="Cnt"></param>
        /// <param name="Sql"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private bool Check_IsNullOrEmpty(string Cnt, string Sql)
        {
            if (string.IsNullOrEmpty(Cnt))
            {
                throw new Exception("连接字符串为空！");
            }
            if (string.IsNullOrEmpty(Sql))
            {
                throw new Exception("SQL字符串为空！");
            }
            return true;
        }
        /// <summary>
        /// adapter.Fill(dt);
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private DataTable Adapter_Fill(string Sql, Action<OleDbParameterCollection> action = null)
        {
            try
            {
                //检查连接字符串、SQL语句是否为空，为空抛出异常
                Check_IsNullOrEmpty(ConnectionString, Sql);
                Check_IsSelect(Sql);
                using (OleDbConnection Cnt = new OleDbConnection(ConnectionString))
                {
                    OleDbCommand cmd = Cnt.CreateCommand();  //创建命令对象
                    cmd.CommandType = CmdType;
                    cmd.CommandTimeout = Timeout;
                    cmd.CommandText = Sql;//sql语句
                    action?.Invoke(cmd.Parameters);// 进行参数化赋值
                    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);//构造SqlDataAdapter
                    DataTable dt = new DataTable();
                    // 如果Fill方法发现连接尚未打开，它将隐式的打开并关闭 Connection
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (ArgumentException e)
            {
                throw e;
            }
            catch (OleDbException e) 
            {
                throw e;
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }

        /// <summary>
        /// adapter.Fill(Start, Count, dt);
        /// </summary>
        /// <param name="Start"></param>
        /// <param name="Count"></param>
        /// <param name="Sql"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private DataTable Adapter_Fill(int Start, int Count, string Sql, Action<OleDbParameterCollection> action = null)
        {
            try
            {
                //检查连接字符串、SQL语句是否为空，为空抛出异常
                Check_IsNullOrEmpty(ConnectionString, Sql);
                Check_IsSelect(Sql);
                using (OleDbConnection Cnt = new OleDbConnection(ConnectionString))
                {
                    OleDbCommand cmd = Cnt.CreateCommand();  //创建命令对象
                    cmd.CommandType = CmdType;
                    cmd.CommandTimeout = Timeout;
                    cmd.CommandText = Sql;//sql语句
                    action?.Invoke(cmd.Parameters);// 进行参数化赋值
                    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);//构造SqlDataAdapter
                    DataTable dt = new DataTable();
                    adapter.Fill(Start, Count, dt);
                    return dt;
                }
            }
            catch (OleDbException e) 
            {
                throw e;
            }
            catch (ArgumentException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// cmd.ExecuteNonQuery();
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private int ExecuteNonQuery(string Sql, Action<OleDbParameterCollection> action = null)
        {

            int influenceRows = -1;
            try
            {
                //检查连接字符串、SQL语句是否为空，为空抛出异常
                Check_IsNullOrEmpty(ConnectionString, Sql);
                Check_IsNotSelect(Sql);
                using (OleDbConnection Cnt = new OleDbConnection(ConnectionString))
                {
                    OleDbCommand cmd = new OleDbCommand(Sql, Cnt);
                    cmd.CommandType = CmdType;
                    cmd.CommandTimeout = Timeout;
                    action?.Invoke(cmd.Parameters);// 进行参数化赋值
                    Cnt.Open();
                    influenceRows = cmd.ExecuteNonQuery();
                    Cnt.Close();
                    return influenceRows;
                }
            }
            catch (OleDbException e) 
            {
                throw e;
            }
            catch (ArgumentException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// cmd.ExecuteScalar();
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private object ExecuteScalar(string Sql, Action<OleDbParameterCollection> action = null)
        {
            try
            {
                //检查连接字符串、SQL语句是否为空，为空抛出异常
                Check_IsNullOrEmpty(ConnectionString, Sql);
                Check_IsSelect(Sql);
                using (OleDbConnection Cnt = new OleDbConnection(ConnectionString))
                {
                    OleDbCommand cmd = new OleDbCommand(Sql, Cnt);
                    cmd.CommandType = CmdType;
                    cmd.CommandTimeout = Timeout;
                    action?.Invoke(cmd.Parameters);// 进行参数化赋值
                    Cnt.Open();
                    object firstRowColumnValue = cmd.ExecuteScalar();
                    Cnt.Close();
                    return firstRowColumnValue;
                }
            }
            catch (OleDbException e) 
            {
                throw e;
            }
            catch (ArgumentException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 返回(该工作簿)所有工作表的元信息表
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllTableInfo()//有问题会返回相应的FilterDatabase
        {
            DataTable dt = new DataTable();
            try
            {
                using (OleDbConnection Cnt = new OleDbConnection(ConnectionString))
                {
                    Cnt.Open();
                    dt = Cnt.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    Cnt.Close();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }
    }
}
